package com.exam.dao;

import com.exam.domain.entity.Question;
import com.exam.domain.entity.Record;
import com.exam.domain.entity.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class RecordDao {

    public List<Record> getExamRecordListByStudentId(Integer studentId) {
        String sql = "SELECT * FROM exam_records WHERE student_id = ? order by submitted_at desc";
        // 执行查询操作，返回结果集
        // 执行查询操作，返回 ResultSet
        List<Record> records = new ArrayList<>();
        try {
            Connection connection = DBUtil.getConnection();
            ResultSet rs = DBUtil.query(connection, sql, studentId);
            while (rs.next()) {
                // created_by, question_text, correct_option, option_a, option_b, option_c, option_d
                // 结果集中有数据，映射到 User 对象
                Record record = new Record();
                record.setExamId(rs.getInt("exam_id"));
                record.setStudentId(rs.getInt("student_id"));
                record.setTotalScore(rs.getInt("total_score"));
                record.setSubmittedAt(rs.getTimestamp("submitted_at"));
                record.setRecordId(rs.getInt("record_id"));
                records.add(record);
            }
            return records;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("查询学生考试记录失败", e);
        }
    }

    public Record getExamRecordById(Integer recordId) {
        String sql = "SELECT * FROM exam_records WHERE record_id = ?";
        // 执行查询操作，返回结果集
        // 执行查询操作，返回 ResultSet
        try {
            Connection connection = DBUtil.getConnection();
            ResultSet rs = DBUtil.query(connection, sql, recordId);
            Record record = new Record();
            if (rs.next()) {
                // created_by, question_text, correct_option, option_a, option_b, option_c, option_d
                // 结果集中有数据，映射到 User 对象
                record.setExamId(rs.getInt("exam_id"));
                record.setStudentId(rs.getInt("student_id"));
                record.setTotalScore(rs.getInt("total_score"));
                record.setSubmittedAt(rs.getTimestamp("submitted_at"));
                record.setRecordId(rs.getInt("record_id"));
                // 返回查询到的用户
                return record;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("查询学生考试记录失败", e);
        }
        // 如果没有找到用户，返回 null
        return null;
    }

    public Record getStudentExamRecord(Integer examId, Integer studentId) {
        String sql = "SELECT * FROM exam_records WHERE exam_id = ? AND student_id = ?";
        // 执行查询操作，返回结果集
        // 执行查询操作，返回 ResultSet
        try (Connection connection = DBUtil.getConnection();
             ResultSet rs = DBUtil.query(connection, sql, examId, studentId)){
            Record record = new Record();
            if (rs.next()) {
                // created_by, question_text, correct_option, option_a, option_b, option_c, option_d
                // 结果集中有数据，映射到 User 对象
                record.setExamId(rs.getInt("exam_id"));
                record.setStudentId(rs.getInt("student_id"));
                record.setTotalScore(rs.getInt("total_score"));
                record.setSubmittedAt(rs.getTimestamp("submitted_at"));
                record.setRecordId(rs.getInt("record_id"));
                // 返回查询到的用户
                return record;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("查询学生考试记录失败", e);
        }
        // 如果没有找到用户，返回 null
        return null;
    }

    public List<Record> getExamRecordListByExamId(Integer examId) {
        String sql = "SELECT * FROM exam_records WHERE exam_id = ? ORDER BY submitted_at DESC";
        List<Record> records = new ArrayList<>();

        // 使用 try-with-resources 确保资源自动关闭
        try (Connection connection = DBUtil.getConnection();
             PreparedStatement stmt = connection.prepareStatement(sql)) {

            // 设置查询条件（examId）
            stmt.setInt(1, examId);
            try (ResultSet rs = stmt.executeQuery()) {
                // 遍历结果集，构建 Record 对象
                while (rs.next()) {
                    Record record = new Record();
                    record.setExamId(rs.getInt("exam_id"));
                    record.setStudentId(rs.getInt("student_id"));
                    record.setTotalScore(rs.getInt("total_score"));
                    record.setSubmittedAt(rs.getTimestamp("submitted_at"));
                    record.setRecordId(rs.getInt("record_id"));

                    // 查询学生信息
                    String studentSql = "SELECT * FROM student_infos WHERE student_id = ?";
                    try (PreparedStatement studentStmt = connection.prepareStatement(studentSql)) {
                        studentStmt.setInt(1, record.getStudentId());
                        try (ResultSet studentRs = studentStmt.executeQuery()) {
                            // 如果查询到学生信息，设置学生姓名
                            if (studentRs.next()) {
                                Student student = new Student();
                                student.setStudentName(studentRs.getString("student_name"));
                                student.setStudentNo(studentRs.getString("student_no"));
                                student.setStudentMajor(studentRs.getString("student_major"));
                                record.setStudent(student);
                            }
                        }
                    }

                    // 添加到记录列表
                    records.add(record);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("查询学生考试记录失败", e);
        }

        return records;
    }

}
